package com.ftqh.tutorial.security.repository;

import com.ftqh.tutorial.security.entity.Role;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.Set;

/**
 * Created by Administrator on 2016/12/27.
 */
@Transactional
public interface RoleRepository extends JpaRepository<Role, Integer> {

//    @Query(value="select r.id, r.name,r.created_at,u.last_modified from SYS_SECURITY_ROLE as r"
//            +" left join SYS_SECURITY_AUTHORITY as a on r.id = a.ROLE_ID"
//            +" left join SYS_SECURITY_USER as u on a.USER_ID = u.id"
//            +" where u.id=?1 "
//            +" union all"
//            +" select r.id, r.name,r.created_at,u.last_modified from SYS_SECURITY_ROLE as r "
//            +" left join SYS_SECURITY_GROUP_AUTHORITY as ga on r.id = ga.ROLE_ID"
//            +" left join SYS_SECURITY_GROUP as g on ga.GROUP_ID = g.id"
//            +" left join SYS_SECURITY_GROUP_USER as gu on g.ID = gu.GROUP_ID"
//            +" left join SYS_SECURITY_USER as u on gu.USER_ID = u.id"
//            +" where u.id=?1 ",nativeQuery=true)
//    Set<Role> findByUserId(Integer userId);
    @Query("select new com.ftqh.tutorial.security.entity.Role(r.id, r.name) from User u JOIN u.groups g JOIN g.roles r WHERE u.id=?1 ")
    Set<Role> findGroupRolesByUserId(Integer userId);

    /**
     * Just for testing
     * @param name
     * @return
     */
    @Query("select r.name from Role r where r.name like %?1%")
    Set<String> findByName(String name);
}
